﻿using System.Collections;
using System.Data;
using Microsoft.Practices.Unity;
using PicStore.BLL.Interface;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PicStore.DAL;
using PicStore.Model;

namespace PicStore.BLL.Impl
{
    public class ReportService : BaseService, IReportService
    {
        [Dependency]
        public ICategoryService CategoryService { get; set; }

        public DataTable GetFrontReport()
        {
            var categoryItemFrontList = CategoryService.GetItemsByCategoryId((int)BaseCategoryEnum.Front);

            DataTable table = new DataTable();
            table.Columns.Add("Front");
            table.Columns.Add("2010");
            table.Columns.Add("2011");
            table.Columns.Add("2012");
            table.Columns.Add("2013");

            if (DateTime.Now.Year == 2013)
            {
                var list = (from r in dbContext.vw_Report_Front_2013
                        select r).ToList();

                
                foreach (var record in list)
                {
                    DataRow row = table.NewRow();
                    string itemName = string.Empty;
                    foreach (var categoryInfo in categoryItemFrontList)
                    {
                        if (record.Front == categoryInfo.ItemValue)
                        {
                            itemName = categoryInfo.ItemName;
                            break;
                        }
                    }
                    row["Front"] = itemName;
                    row["2010"] = (record._2010*100).ToString("N");
                    row["2011"] = (record._2011 * 100).ToString("N");
                    row["2012"] = (record._2012 * 100).ToString("N");
                    row["2013"] = (record._2013 * 100).ToString("N");

                    table.Rows.Add(row);
                }
            }
            if (DateTime.Now.Year == 2014)
            {
                var list = (from r in dbContext.vw_Report_Front_2014
                            select r).ToList();


                foreach (var record in list)
                {
                    DataRow row = table.NewRow();
                    string itemName = string.Empty;
                    foreach (var categoryInfo in categoryItemFrontList)
                    {
                        if (record.Front == categoryInfo.ItemValue)
                        {
                            itemName = categoryInfo.ItemName;
                            break;
                        }
                    }
                    row["Front"] = itemName;
                    row["2010"] = string.Format("{0}%", (record._2010 * 100));
                    row["2011"] = string.Format("{0}%", (record._2011 * 100));
                    row["2012"] = string.Format("{0}%", (record._2012 * 100));
                    row["2013"] = string.Format("{0}%", (record._2013 * 100));
                    row["2014"] = string.Format("{0}%", (record._2014 * 100));

                    table.Rows.Add(row);
                }
            }

            return table;
        }

        public List<ReportItem> GetFrontReportSpecial(BaseCategoryEnum baseCategory, int monthValue)
        {
            int categoryValue = (int) baseCategory;
            string strSqlFormat = "select item.CategoryItemName, table2.takePhotoYear as Year, cast(cast(table2.CategoryCount as numeric(10,3))/cast(table1.YearCount as numeric(10,3)) as numeric(10,4)) as Value, table2.CategoryCount from "
                    + "(select YEAR(TakePhotoDate) as takePhotoYear, COUNT(PictureId) as YearCount from Picture where MONTH(TakePhotoDate)={2}  group by YEAR(TakePhotoDate)) table1 "
                    + "inner join(select {0}, YEAR(TakePhotoDate) as takePhotoYear, COUNT(PictureId) as CategoryCount from Picture where MONTH(TakePhotoDate)={2} group by YEAR(TakePhotoDate), {0} ) table2 "
                    + "on table1.takePhotoYear = table2.takePhotoYear inner join CategoryItem item on item.CategoryId={1} and item.CategoryItemValue = table2.{0} "
                    + "order by table2.takePhotoYear ";

            string strSql = string.Format(strSqlFormat, baseCategory.ToString(), categoryValue, monthValue);
            var query = dbContext.ExecuteQuery<ReportItem>(strSql);
            List<ReportItem> list = new List<ReportItem>();
            foreach (var item in query)
            {
                item.Value *= 100;
                item.PercentValue = item.Value.ToString("N") + "%";
                list.Add(item);
            }

            return list;
        }
    }
}
